package at.fhj.antesk.steamvaluecalc.implementations;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import at.fhj.antesk.steamvaluecalc.data.Game;
import at.fhj.antesk.steamvaluecalc.interfaces.DataHandler;


public class DataHandlerSQLiteImpl implements DataHandler {

    private Connection connection;
    private Statement statement;

    public DataHandlerSQLiteImpl(String dbFilePath) {
        try {
            Class.forName("org.sqlite.JDBC");

            connection = DriverManager.getConnection("jdbc:sqlite:data.db");
            statement = connection.createStatement();

            String sqlStatement = createStringFromFile(dbFilePath);
            statement.executeUpdate(sqlStatement);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private String createStringFromFile(String dbFilePath) throws FileNotFoundException, IOException {
        File schema = new File(dbFilePath);
        BufferedReader br = new BufferedReader(new FileReader(schema));
        StringBuilder sb = new StringBuilder();
        String line = br.readLine();
        while (line != null) {
            sb.append(line);
            sb.append("\n");
            line = br.readLine();
        }
        br.close();
        return sb.toString();
    }

    public List<Game> loadGames() {
        List<Game> games = new ArrayList<Game>();

        try {
            ResultSet resultSet = statement.executeQuery("SELECT * FROM games");
            while (resultSet.next()) {
                Game game = new Game();
                game.setId(resultSet.getString("appId"));
                game.setName(resultSet.getString("name"));
                games.add(game);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return games;
    }

    public void addGame(Game game) {
        String sql = "INSERT INTO games VALUES (?,?,?,?,?,?,?)";

        try {
            PreparedStatement prepareStatement = connection.prepareStatement(sql);
            prepareStatement.setInt(1, Integer.parseInt(game.getId()));
            prepareStatement.setString(2, game.getName());
            prepareStatement.setString(3, game.getLogo());
            prepareStatement.setString(4, game.getStoreLink());
            prepareStatement.setDouble(5, game.getHoursOnRecord());
            // prepareStatement.setDouble(6, game.getHourValue());
            prepareStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void storeGames(List<Game> games) {
        // TODO Auto-generated method stub

    }

    public void updateGame(Game game) {
        // TODO Auto-generated method stub

    }

    public void updateGamesInFile(List<Game> gamesToUpdate) {
        // TODO Auto-generated method stub

    }

    public void close() throws SQLException {
        connection.close();
    }

}
